package com.bringway.p2p.dao.impl;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import net.sf.json.JSONObject;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

import com.bringway.framework.dao.P2PJdbcDao;
import com.bringway.p2p.dao.CjjgDao;

/**
 * 
 * @author zhai
 * @version 创建时间：2015年12月14日 上午9:34:42
 * @功能：抽奖结果页面
 */

@Repository
public class CjjgDaoImpl  extends P2PJdbcDao implements CjjgDao{

	Logger logger=Logger.getLogger(CjjgDaoImpl.class);
	/**
	 * @author zhai
	 * CjjgDao 的实现类
	 * 查询抽奖结果的SQL语句
	 */
	public List queryCjjgList(JSONObject params) {

		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		if(StringUtils.isNotEmpty(startDate)){
			startDate=startDate+" 00:00:00";
		}
		if(StringUtils.isNotEmpty(endDate)){
			endDate=endDate+" 23:59:59";
		}
		String jxx = params.optString("jxx");
		String username = params.optString("username");
		String sjhm = params.optString("sjhm");
		String vip_dj = params.optString("vip_dj");
		
		
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("START", start);
		paramMap.put("LIMIT", limit);
		paramMap.put("USERNAME", username);
		paramMap.put("SJHM", sjhm);
		paramMap.put("JXX", jxx);
		paramMap.put("vip_dj", vip_dj);

		
		
		sql.append(" SELECT * ");
		sql.append(" FROM v_p2p_vip_cjjg v left join v_p2p_user_tjr t3 on v.USERNAME=t3.USER_NAME  where  1=1 ");
		if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND FFSJ >= :startDate");
			paramMap.put("startDate",startDate);
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND FFSJ <= :endDate");
			paramMap.put("endDate",endDate);
		}
		if(StringUtils.isNotEmpty(jxx)){
			sql.append(" AND JX = :jxx ");
		}
		if(StringUtils.isNotEmpty(username)){
			sql.append(" AND USERNAME like "+ "'%"+username+"%'");
		}
		if(StringUtils.isNotEmpty(sjhm)){
			sql.append(" AND t3.SJHM like "+ "'%"+sjhm+"%'");
		}
		
		if((StringUtils.isNotEmpty(vip_dj))&&(vip_dj.equals("0"))){
			sql.append(" and SFFF is null ");
		}
		
		if((StringUtils.isNotEmpty(vip_dj))&&(vip_dj.equals("1"))){
			sql.append(" and SFFF=1");
		}
		sql.append(" order by CJSJ desc LIMIT :START,:LIMIT ");
		return this.getNamedParameterJdbcTemplate().queryForList(
				sql.toString(), paramMap);

	}

	/**
	 * @author zhai
	 * 获取抽奖结果的数据个数
	 */
	@SuppressWarnings("deprecation")
	@Override
	public int getCjjgCount(JSONObject params) {
		
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		if(StringUtils.isNotEmpty(startDate)){
			startDate=startDate+" 00:00:00";
		}
		if(StringUtils.isNotEmpty(endDate)){
			endDate=endDate+" 23:59:59";
		}
		String jxx = params.optString("jxx");
		String username = params.optString("username");
		String sjhm = params.optString("sjhm");
		String vip_dj = params.optString("vip_dj");
		
		
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("START", start);
		paramMap.put("LIMIT", limit);
		paramMap.put("USERNAME", username);
		paramMap.put("SJHM", sjhm);
		paramMap.put("JXX", jxx);
		paramMap.put("vip_dj", vip_dj);
		
		sql.append(" SELECT COUNT(*) CT ");
		sql.append(" FROM v_p2p_vip_cjjg where 1=1 ");
		if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND FFSJ >= :startDate");
			paramMap.put("startDate",startDate);
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND FFSJ <= :endDate");
			paramMap.put("endDate",endDate);
		}
		if(StringUtils.isNotEmpty(jxx)){
			sql.append(" AND JX = :jxx ");
		}
		if(StringUtils.isNotEmpty(username)){
			sql.append(" AND USERNAME like "+ "'%"+username+"%'");
		}
		if(StringUtils.isNotEmpty(sjhm)){
			sql.append(" AND SFFF like "+ "'%"+sjhm+"%'");
		}
		
		if((StringUtils.isNotEmpty(vip_dj))&&(vip_dj.equals("0"))){
			sql.append(" and SFFF is null ");
		}
		
		if((StringUtils.isNotEmpty(vip_dj))&&(vip_dj.equals("1"))){
			sql.append(" and SFFF=1");
		}
		return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}

	public List queryCjjlList(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		if(StringUtils.isNotEmpty(startDate)){
			startDate=startDate+" 00:00:00";
		}
		if(StringUtils.isNotEmpty(endDate)){
			endDate=endDate+" 23:59:59";
		}
		String cjzt = params.optString("cjzt");
		String username = params.optString("username");
		String sjhm = params.optString("sjhm");
		String lqzt = params.optString("lqzt");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("start", start);
		paramMap.put("limit", limit);
		sql.append(" SELECT ID, USERNAME, SJHM, REAL_NAME, CJ_TYPE, JX,date_format(CJSJ,'%Y-%m-%d %H:%i:%s') CJSJ, LQ_STATE, LQ_FGSMD, date_format(USER_CZSJ,'%Y-%m-%d %H:%i:%s') USER_CZSJ, FF_GYH, date_format(FFSJ,'%Y-%m-%d %H:%i:%s')FFSJ, LY_TYPE ");
		sql.append(" FROM t_zhfw_cjjg  where  1=1 ");
		if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND CJSJ >= :startDate");
			paramMap.put("startDate",startDate);
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND CJSJ <= :endDate");
			paramMap.put("endDate",endDate);
		}
		if(StringUtils.isNotEmpty(cjzt)){
			sql.append(" AND CJ_TYPE = :cjzt ");
			paramMap.put("cjzt", cjzt);
		}
		if(StringUtils.isNotEmpty(lqzt)){
			sql.append(" AND LQ_STATE = :lqzt ");
			paramMap.put("lqzt", lqzt);
		}
		if(StringUtils.isNotEmpty(username)){
			sql.append(" AND USERNAME like "+ "'%"+username+"%'");
			paramMap.put("username", username);
		}
		if(StringUtils.isNotEmpty(sjhm)){
			sql.append(" AND SJHM like "+ "'%"+sjhm+"%'");
			paramMap.put("sjhm", sjhm);
		}
		sql.append(" order by CJSJ desc LIMIT :start,:limit ");
		logger.info("queryCjjlList sql is "+sql.toString());
		logger.info("queryCjjlList paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().queryForList(
				sql.toString(), paramMap);

	}
	@SuppressWarnings("deprecation")
	@Override
	public int getCjjlCount(JSONObject params) {
		String cjzt = params.optString("cjzt");
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		if(StringUtils.isNotEmpty(startDate)){
			startDate=startDate+" 00:00:00";
		}
		if(StringUtils.isNotEmpty(endDate)){
			endDate=endDate+" 23:59:59";
		}
		String username = params.optString("username");
		String sjhm = params.optString("sjhm");
		String lqzt = params.optString("lqzt");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		sql.append(" SELECT COUNT(*)");
		sql.append(" FROM t_zhfw_cjjg where 1=1 ");
		if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND CJSJ >= :startDate");
			paramMap.put("startDate",startDate);
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND CJSJ <= :endDate");
			paramMap.put("endDate",endDate);
		}
		if(StringUtils.isNotEmpty(cjzt)){
			sql.append(" AND CJ_TYPE = :cjzt ");
			paramMap.put("cjzt", cjzt);
		}
		if(StringUtils.isNotEmpty(lqzt)){
			sql.append(" AND LQ_STATE = :lqzt ");
			paramMap.put("lqzt", lqzt);
		}
		if(StringUtils.isNotEmpty(username)){
			sql.append(" AND USERNAME like "+ "'%"+username+"%'");
			paramMap.put("username", username);
		}
		if(StringUtils.isNotEmpty(sjhm)){
			sql.append(" AND SJHM like "+ "'%"+sjhm+"%'");
			paramMap.put("sjhm", sjhm);
		}
		logger.info("getCjjlCount sql is "+sql.toString());
		logger.info("getCjjlCount paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}
	
	
	
	public List queryCjzgList(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String username = params.optString("username");
		String sjhm = params.optString("sjhm");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("start", start);
		paramMap.put("limit", limit);
		sql.append(" SELECT t1.ID, t1.USER_NAME, t1.SS_KY_CJ_CHANCE_NUM, t1.SUM_CJ_CHANCE_NUM,t2.SJHM ");
		sql.append(" FROM t_zhfw_cjzg t1 left join  t_zhfw_user t2  on t1.USER_NAME=t2.USER_NAME and t2.DISABLED='1'  where  1=1 ");
		if(StringUtils.isNotEmpty(username)){
			sql.append(" AND t1.USER_NAME like "+ "'%"+username+"%'");
			paramMap.put("username", username);
		}
		if(StringUtils.isNotEmpty(sjhm)){
			sql.append(" AND t2.SJHM = :sjhm ");
			paramMap.put("sjhm", sjhm);
		}
		sql.append("  LIMIT :start,:limit ");
		logger.info("queryCjzgList sql is "+sql.toString());
		logger.info("queryCjzgList paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().queryForList(
				sql.toString(), paramMap);

	}
	@SuppressWarnings("deprecation")
	@Override
	public int getCjzgCount(JSONObject params) {
		String username = params.optString("username");
		String sjhm = params.optString("sjhm");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		sql.append(" SELECT COUNT(*)");
		sql.append(" FROM t_zhfw_cjzg t1 left join  t_zhfw_user t2  on t1.USER_NAME=t2.USER_NAME and t2.DISABLED='1'  where  1=1");
		if(StringUtils.isNotEmpty(username)){
			sql.append(" AND t1.USER_NAME like "+ "'%"+username+"%'");
			paramMap.put("username", username);
		}
		if(StringUtils.isNotEmpty(sjhm)){
			sql.append(" AND t2.SJHM = :sjhm ");
			paramMap.put("sjhm", sjhm);
		}
		logger.info("getCjzgCount sql is "+sql.toString());
		logger.info("getCjzgCount paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}
	
}
